﻿CREATE PROCEDURE [dbo].[proc_Customer_DCreate_TongJi]
(
	@CompanyId int,
	@DepartmentId int,
	@Stext nvarchar(50),
	@StartDate nvarchar(50),
	@EndDate nvarchar (50)	
)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	--取该部门所有的员工
	;WITH List2 As (
		Select e.UserName,e.EName,
		IsNull(d.DName,'') As DName From Employee e
		Left Join Department d On e.DepartmentId=d.id
		Where e.CompanyId=@CompanyId And Left(d.bmbh,Len(@bmbh_T))=@bmbh_T
	),
	--取这些员工保护或跟踪中的客户
	List1 As (Select C.Id,C.CustomerName,C.OrdersCount,
	    List2.DName,List2.EName
		From Customer C,List2
		Where CompanyId=@CompanyId And C.CreateDate>=@sRq And C.CreateDate<=@eRq
		And C.Audit = 1 And C.CreateName=List2.UserName
		And (@Stext='' Or List2.EName Like '%' + @SText + '%' Or C.CustomerName Like '%' + @SText + '%')
	),
	List As (Select --ROW_NUMBER() OVER (ORDER BY Count(0) Desc)AS Row,
		List1.DName As DepartmentName,List1.EName As CreateName,
		Count(0) As DCustomerCount,
		Sum(OrdersCount) As OrderCount
		From List1 Group By List1.DName,List1.EName
	)

	Select *,(Select Count(0) From List) As RecordCount
	From List
	--Where Row between @StartIndex And @EndIndex
	Order By DCustomerCount Desc,OrderCount Desc
End
